﻿using System.Data.SQLite;
using System;
using System.IO;
using System.Diagnostics;
using CSharp.Libs.Koc.ItemClasses;
using System.Collections.ObjectModel;
using CSharp.Libs.Koc;

namespace CSharp.Libs.Koc
{
    public partial class DatabaseOperations
    {
        #region declarations
        private Kernel _KRNL = null;
        private SQLiteConnection _CON = null;
        private Stopwatch _SW = new Stopwatch();

        private String _strDatabaseFileName = "";
        private string _strCreateDbSql = ""; //19.08.2012
        private Boolean _fDbOpen = false;
        private Boolean _fTimeDiagnosis = false;

        #region events
        public delegate void LogEntryEventHandler(object sender, LogEntryEventArgs e);
        public delegate void TaskEventHandler(object sender, TaskEventArgs e);
        public delegate void DatabaseFileMessageEventHandler(object sender, DatabaseFileMessageEventArgs e);

        public event LogEntryEventHandler LogEntry;
        public event TaskEventHandler ehTask;
        public event DatabaseFileMessageEventHandler databaseFileMessage;

        private void ThrowLogEntryEvent(DateTime EntryDateTime, String Message)
        {
            if (LogEntry != null)
            {
                LogEntry(this, new LogEntryEventArgs(EntryDateTime, Message));
            }
        }
        private void ThrowLogEntryEvent(String Message)
        {
            if (LogEntry != null)
            {
                LogEntry(this, new LogEntryEventArgs(Message));
            }
        }

        private void ThrowTaskEvent(TaskEventArgs.enmTaskAction taTaskAction, String TaskMessage)
        {
            if (ehTask != null)
            {
                ehTask(this, new TaskEventArgs(taTaskAction, TaskMessage));
            }
        }

        private void ThrowDatabaseFileMessageEvent(String Message)
        {
            if (databaseFileMessage != null)
            {
                databaseFileMessage(this, new DatabaseFileMessageEventArgs(Message));
            }
        }
        #endregion
        #endregion



        #region constructor / terminator
        public DatabaseOperations(
            Kernel KRNL)
        {
            try
            {
                _KRNL = KRNL;
            }
            catch
            {
            }
        }

        ~DatabaseOperations()
        {
            try
            {
                if (_SW.IsRunning)
                {
                    _SW.Stop();
                }
                _SW = null;

                if (_CON != null)
                {
                    _CON.Close();
                    _CON.Dispose();
                    _CON = null;
                }
            }
            catch (Exception ex)
            {
                _KRNL.EC.AddException(ex, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
            }
        }
        #endregion



        #region event handling
        #endregion



        #region private functions
        #region *   Database Creation
        /// <summary>sets the old default database creation sql string</summary>
        /// <history>created  19.08.2012</history>
        private void _setStandardDatabaseSQLString()
        {
            try
            {
                _strCreateDbSql = String.Concat(
                                    "CREATE TABLE [tblAutoTransport] (",
                                    "[AutoTransportID] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,",
                                    "[ServerID] INTEGER DEFAULT '''0''' NOT NULL,",
                                    "[CityID] INTEGER DEFAULT '''0''' NOT NULL,",
                                    "[TransportActive] INTEGER DEFAULT '''0''' NOT NULL,",
                                    "[DestinationXCoord] INTEGER DEFAULT '0' NULL,",
                                    "[DestinationYCoord] INTEGER DEFAULT '0' NULL,",
                                    "[TransportGold] INTEGER DEFAULT '''0''' NOT NULL,",
                                    "[TransportFood] INTEGER DEFAULT '''0''' NOT NULL,",
                                    "[TransportWood] INTEGER DEFAULT '''0''' NOT NULL,",
                                    "[TransportStone] INTEGER DEFAULT '''0''' NOT NULL,",
                                    "[TransportOre] INTEGER DEFAULT '''0''' NOT NULL,",
                                    "[GoldMin] INTEGER DEFAULT '0' NOT NULL,",
                                    "[GoldMax] INTEGER DEFAULT '0' NOT NULL,",
                                    "[FoodMin] INTEGER DEFAULT '0' NOT NULL,",
                                    "[FoodMax] INTEGER DEFAULT '0' NOT NULL,",
                                    "[WoodMin] INTEGER DEFAULT '0' NOT NULL,",
                                    "[WoodMax] INTEGER DEFAULT '0' NOT NULL,",
                                    "[StoneMin] INTEGER DEFAULT '0' NOT NULL,",
                                    "[StoneMax] INTEGER DEFAULT '0' NOT NULL,",
                                    "[OreMin] INTEGER DEFAULT '0' NOT NULL,",
                                    "[OreMax] INTEGER DEFAULT '0' NOT NULL",
                                    ");",
                                    Environment.NewLine,
                                    "CREATE INDEX [idxAutoTransport] ON [tblAutoTransport](",
                                    "[ServerID]  DESC,",
                                    "[CityID]  DESC",
                                    ");",
                                    Environment.NewLine,
                                    "CREATE TABLE [tblMarches] (",
                                    "[MarchID] INTEGER  PRIMARY KEY NOT NULL, ",
                                    "[TileID] INTEGER  NULL, ",
                                    "[TileType] INTEGER  NULL, ",
                                    "[TileLevel] INTEGER  NULL, ",
                                    "[InitTS] INTEGER  NULL, ",
                                    "[Distance] FLOAT  NULL, ",
                                    "[Speed] INTEGER  NULL, ",
                                    "[ETA] INTEGER  NULL, ",
                                    "[Delay] INTEGER  NULL, ",
                                    "[LiftFog] INTEGER  NULL, ",
                                    "[AtkBoostTime] INTEGER  NULL, ",
                                    "[Atk2BoostTime] INTEGER  NULL, ",
                                    "[DefBoostTime] INTEGER  NULL, ",
                                    "[Def2BoostTime] INTEGER  NULL, ",
                                    "[KnightCombatBoostTime] INTEGER  NULL, ",
                                    "[MarchUnixTime] INTEGER  NULL, ",
                                    "[ReqMicroTime] FLOAT  NULL, ",
                                    "[ResMicroTime] FLOAT  NULL, ",
                                    "[ServerID] INTEGER  NULL, ",
                                    "[CityID] INTEGER  NULL, ",
                                    "[KnightID] INTEGER  NULL, ",
                                    "[DestinationXCoord] INTEGER  NULL, ",
                                    "[DestinationYCoord] INTEGER  NULL, ",
                                    "[MarchType] INTEGER  NULL, ",
                                    "[Gold] INTEGER  NULL, ",
                                    "[R1] INTEGER  NULL, ",
                                    "[R2] INTEGER  NULL, ",
                                    "[R3] INTEGER  NULL, ",
                                    "[R4] INTEGER  NULL, ",
                                    "[Items] NVARCHAR(256)  NULL, ",
                                    "[U1] INTEGER  NULL, ",
                                    "[U2] INTEGER  NULL, ",
                                    "[U3] INTEGER  NULL, ",
                                    "[U4] INTEGER  NULL, ",
                                    "[U5] INTEGER  NULL, ",
                                    "[U6] INTEGER  NULL, ",
                                    "[U7] INTEGER  NULL, ",
                                    "[U8] INTEGER  NULL, ",
                                    "[U9] INTEGER  NULL, ",
                                    "[U10] INTEGER  NULL, ",
                                    "[U11] INTEGER  NULL, ",
                                    "[U12] INTEGER  NULL); ",
                                    Environment.NewLine,
                                    "CREATE INDEX [idxMarches] ON [tblMarches]( ",
                                    "[MarchID]  ASC, ",
                                    "[ETA]  ASC, ",
                                    "[MarchUnixTime]  ASC, ",
                                    "[InitTS]  ASC, ",
                                    "[TileID]  ASC, ",
                                    "[ServerID]  ASC, ",
                                    "[CityID]  ASC, ",
                                    "[DestinationXCoord]  ASC, ",
                                    "[DestinationYCoord]  ASC, ",
                                    "[KnightID]  ASC); ",
                                    Environment.NewLine,
                                    "CREATE TABLE [tblServerActive] ( ",
                                    "[ServerActiveID] INTEGER DEFAULT '1' NOT NULL PRIMARY KEY AUTOINCREMENT, ",
                                    "[ServerNumber] INTEGER  UNIQUE NOT NULL, ",
                                    "[ServerActive] IMTEGER  NOT NULL);");
            }
            catch (Exception ex)
            {
                _KRNL.EC.AddException(ex, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
            }
        }

        /// <summary>_InitDB</summary>
        /// <history>10.04.2011</history>
        private void _InitDB(
            String DatabaseFileName)
        {
            try
            {
                _strDatabaseFileName = DatabaseFileName;

                if (!File.Exists(_strDatabaseFileName))
                {
                    _CreateDB();
                }

                if (File.Exists(_strDatabaseFileName))
                {
                    _OpenDB();
                }
                else
                {
                    FileNotFoundException _exFileNotFound = new FileNotFoundException(
                        "Specified File couldn't be created.",
                        _strDatabaseFileName);
                    _KRNL.EC.AddException(_exFileNotFound, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
                }
            }
            catch (Exception ex)
            {
                _KRNL.EC.AddException(ex, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
            }
        }

        /// <summary>_CreateDB</summary>
        /// <history>10.04.2011</history>
        private void _CreateDB()
        {
            try
            {
                //Make sure DB does not exist
                if (!File.Exists(_strDatabaseFileName))
                {
                    using (SQLiteConnection CON = new SQLiteConnection(String.Concat("Data Source=", _strDatabaseFileName)))
                    {
                        CON.Open();

                        using (SQLiteCommand CMD = CON.CreateCommand())
                        {
                            CMD.CommandText = _strCreateDbSql;

                            try
                            {
                                CMD.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                _KRNL.EC.AddException(ex, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
                            }
                        }

                        CON.Close();
                    }
                }
                else
                {
                    _KRNL.EC.AddException(new FileLoadException("ERROR in DatabaseOperations._CreateDB: File already exists so I better do nothing. Otherwise an existing database could get overwritten completely.", _strDatabaseFileName), this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
                }
            }
            catch (Exception ex)
            {
                _KRNL.EC.AddException(ex, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
            }
        }

        /// <summary>_OpenDatabase</summary>
        /// <history>10.04.2011</history>
        private void _OpenDB()
        {
            try
            {
                if (File.Exists(_strDatabaseFileName))
                {
                    if (_fTimeDiagnosis)
                    {
                        _SW.Start();
                    }

                    //Setting Cache to 600MB for performance reasons
                    //Testphase
                    _CON = new SQLiteConnection(String.Concat(
                        "Data Source=", _strDatabaseFileName, ";",
                        "Cache Size=600000;",
                        "Page Size=1024"));
                    _CON.Open();
                    _fDbOpen = true;

                    if (_fTimeDiagnosis)
                    {
                        _SW.Stop();
                        ThrowLogEntryEvent(DateTime.Now, "TimeDiagnosis - _OpenDatabase (" + _strDatabaseFileName + ") - " + _SW.Elapsed.ToString());
                    }
                }
                else
                {
                    _fDbOpen = false;
                    FileNotFoundException _exFileNotFound = new FileNotFoundException(
                        "ERROR opening SQLiteDB: File not found.",
                        _strDatabaseFileName);
                    _KRNL.EC.AddException(_exFileNotFound, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
                    ThrowDatabaseFileMessageEvent("ERROR opening SQLiteDB: File not found (" + _strDatabaseFileName + ").");
                }
            }
            catch (Exception ex)
            {
                _fDbOpen = false;
                _KRNL.EC.AddException(ex, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
                ThrowDatabaseFileMessageEvent("Database could not be opened (" + ex.Message + ")");
            }

            try
            {
                //_InitTableStuff();
                //_GetCatTables();
                //_GetTables();
            }
            catch (Exception ex)
            {
                _KRNL.EC.AddException(ex, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
            }
        }
        #endregion

        #region basic functions
        /// <summary>_GetBoolean</summary>
        /// <param name="Input"></param>
        /// <returns></returns>
        /// <history>created  15.04.2011</history>
        /// <history>modified 25.11.2011: Added evaluating bool as string</history>
        private Boolean _GetBoolean(
            Object Input)
        {
            try
            {
                Int64 RetVal = -1;
                if (Int64.TryParse(Input.ToString(), out RetVal))
                {
                    if (RetVal != 0)
                    {
                        return true;
                    }
                }
                else
                {
                    if (Input.ToString().ToLower() == "true")
                    {
                        return true;
                    }
                }
            }
            catch (Exception ex)
            {
                _KRNL.EC.AddException(ex, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
            }

            return false;
        }

        /// <summary>_GetInt64</summary>
        /// <param name="Input"></param>
        /// <returns></returns>
        /// <history>15.04.2011</history>
        private Int64 _GetInt64(
            Object Input)
        {
            try
            {
                Int64 RetVal = -1;
                Int64.TryParse(Input.ToString(), out RetVal);
                return RetVal;
            }
            catch (Exception ex)
            {
                _KRNL.EC.AddException(ex, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
            }

            return -1;
        }

        /// <summary>_GetDouble</summary>
        /// <param name="Input"></param>
        /// <returns></returns>
        /// <history>15.05.2011</history>
        private Double _GetDouble(
            Object Input)
        {
            try
            {
                Double RetVal = -1;
                Double.TryParse(Input.ToString(), out RetVal);
                return RetVal;
            }
            catch (Exception ex)
            {
                _KRNL.EC.AddException(ex, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
            }

            return -1;
        }

        /// <summary>_SetBooleanString</summary>
        /// <param name="Input"></param>
        /// <param name="asString"></param>
        /// <returns></returns>
        /// <history>created  25.11.2011</history>
        private String _SetBooleanString(
            Boolean Input)
        {
            try
            {
                if (Input)
                {
                    return "True";
                }
            }
            catch (Exception ex)
            {
                _KRNL.EC.AddException(ex, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
            }

            return "False";
        }

        /// <summary>_SetBoolean</summary>
        /// <param name="Input"></param>
        /// <returns></returns>
        /// <history>15.04.2011</history>
        private Int64 _SetBoolean(
            Boolean Input)
        {
            try
            {
                if (Input)
                {
                    return 1;
                }
            }
            catch (Exception ex)
            {
                _KRNL.EC.AddException(ex, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
            }

            return 0;
        }

        /// <summary>_TableExists</summary>
        /// <param name="CON"></param>
        /// <param name="strTableName"></param>
        /// <returns></returns>
        /// <history>02.08.2011</history>
        private Boolean _TableExists(
            SQLiteConnection CON,
            String strTableName)
        {
            SQLiteCommand CMD = new SQLiteCommand(CON);
            CMD.CommandText = String.Concat("SELECT name FROM sqlite_master WHERE name='", strTableName, "'");

            SQLiteDataReader DR = CMD.ExecuteReader();

            if (DR.HasRows)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion
        #endregion



        #region interface
        #region properties
        public String DatabaseFileName { get { return _strDatabaseFileName; } }

        public string CreateDbSql { get { return _strCreateDbSql; } set { _strCreateDbSql = value; } }

        public Boolean DbOpen { get { return _fDbOpen; } }
        #endregion

        #region methods
        public void InitDb(
            String DatabaseFileName)
        {
            _InitDB(DatabaseFileName);
        }

        public void InitDb(
            String DatabaseFileName,
            string CreateSqlDbString)
        {
            _strCreateDbSql = CreateSqlDbString;

            _InitDB(DatabaseFileName);
        }
        #endregion
        #endregion
    }
}
